package jeff.plugin.mybatis.db;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import jeff.plugin.mybatis.db.support.DBFieldTypeTransfer;
import jeff.plugin.mybatis.db.support.DatabaseEnum;
import jeff.plugin.mybatis.db.support.Table;
import jeff.plugin.mybatis.db.support.TableColumn;

/**
 * 数据库操作
 * @author Jeff
 */
public class DBManager {

	private String driverName;
	private String url;
	private Properties properties = new Properties();
	
	private DatabaseEnum databaseType;
	private String catalog = null;
	private String schemaPattern = null;
	
	private Connection conn = null;
	private ResultSet rs = null;
	private Statement stmt = null;
	
	public void open() throws ClassNotFoundException, SQLException{
		autoCheckDriver();
		handleDatabaseInPreUse();
		
		Class.forName(driverName);
		conn = DriverManager.getConnection(url, properties);
	}
	
	private void initPstmt(String sql) throws SQLException{
		if(sql == null){
			return;
		}
		if(stmt == null){
			stmt = conn.createStatement();
		}
	}
	
	public void close() throws SQLException{
		if(conn != null){
			conn.close();
		}
	}
	
	/**
	 * get all tables's properties
	 * @return 返回所有表属性
	 * @throws SQLException
	 */
	public List<Table> getTables() throws SQLException{
		List<Table> tables = new ArrayList<Table>();
		
		DatabaseMetaData metadata = conn.getMetaData();
		rs =  metadata.getTables(catalog, schemaPattern, "%", new String[]{"TABLE", "VIEW"});
		while(rs.next()){
			String tablename = rs.getString("TABLE_NAME");
			String remarks = rs.getString("REMARKS");
			String type = rs.getString("TABLE_TYPE");
			
			Table table = new Table();
			table.setName(tablename);
			table.setType(type);
			table.setRemarks(remarks);
			tables.add(table);
		}
		return tables;
	}
	
	/**
	 * get columns by table's name
	 * @param tablename 表名
	 * @return 返回所有表中字段属性
	 * @throws SQLException sql异常
	 */
	public List<TableColumn> getColumnsByTable(String tablename) throws SQLException{
		List<TableColumn> columns = new ArrayList<TableColumn>();
		DatabaseMetaData metadata = conn.getMetaData();
		
		ResultSet primayKeysRS = metadata.getPrimaryKeys(catalog, schemaPattern, tablename);
		Map<String, Boolean> primayKeyMap = new HashMap<String, Boolean>();
		while(primayKeysRS.next()){
			String columnname = primayKeysRS.getString("COLUMN_NAME");
			primayKeyMap.put(columnname, true);
		}
		
		ResultSet columnRS = metadata.getColumns(catalog, schemaPattern, tablename, "%");
		while(columnRS.next()){
			String columnname = columnRS.getString("COLUMN_NAME");
			int dataType = columnRS.getInt("DATA_TYPE");
			String typeName = columnRS.getString("TYPE_NAME");
			String columnRemarks = columnRS.getString("REMARKS");
			
			TableColumn column = new TableColumn();
			column.setColumnName(columnname);
			column.setDataType(dataType);
			column.setTypeName(typeName);
			column.setRemarks(columnRemarks);
			column.setPrimarykey(primayKeyMap.get(columnname)!=null?primayKeyMap.get(columnname):false);
			columns.add(column);
		}
		
		
		return columns;
	}
	
	/**
	 * get all tables and columns
	 * @return 返回表属性和表中字段属性
	 * @throws SQLException sql异常
	 */
	public List<Table> getTablesAndColumns() throws SQLException{
		List<Table> tables = getTables();
		for(Table table : tables){
			List<TableColumn> columns = getColumnsByTable(table.getName());
			table.setColumns(columns);
		}
		return tables;
	}
	
	/**
	 * 一般sql执行
	 * @param sql
	 * @throws Exception
	 */
	public void execute(String sql) throws Exception{
		initPstmt(sql);
		stmt.execute(sql);
	}
	
	/**
	 * 插入数据
	 * @param sql
	 * @throws Exception
	 */
	public void insert(String sql) throws Exception{
		initPstmt(sql);
		stmt.execute(sql);
	}
	
	/**
	 * 更新数据
	 * @param sql
	 * @throws Exception
	 */
	public void update(String sql) throws Exception{
		initPstmt(sql);
		stmt.executeUpdate(sql);
	}
	
	/**
	 * query data and handle by self
	 * @param sql
	 * @param handler
	 * @throws Exception
	 */
	public void query(String sql , DBResultHandler handler) throws Exception{
		initPstmt(sql);
		rs = stmt.executeQuery(sql);
		handler.hanle(rs);
	}
	
	/**
	 * 根据sql查询实体对象。 1.务必保证java类中变量名都为小写
	 * 2. 该方法只能查询出一个对象
	 * @param sql sql语句
	 * @param cla Java类型
	 * @return 返回实体
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public <T> T queryForObject(String sql, Class<T> cla) throws Exception{
		initPstmt(sql);
		PreparedStatement pstmt = conn.prepareStatement(sql);
		ResultSetMetaData resultSetMetaData =  pstmt.getMetaData();
		rs = pstmt.executeQuery();
		
		Map<String, Field> fieldMap = null;
		int columnCount = resultSetMetaData.getColumnCount();
		
		while(rs.next()){
			if(cla == Integer.class || cla == String.class || cla == Long.class || cla == Double.class){
				return (T)rs.getObject(1);
			}
			if(fieldMap == null){
				fieldMap = new HashMap<String, Field>();
				for(Field field : cla.getDeclaredFields()){
					field.setAccessible(true);
					fieldMap.put(field.getName().toUpperCase(), field);
				}
			}
			
			T obj = cla.newInstance();
			for(int i = 0; i < columnCount; i++){
				String columnName = resultSetMetaData.getColumnName(i+1).toUpperCase();
				Field cfield = fieldMap.get(columnName);
				if(cfield == null){
					continue;
				}
				Object data = DBFieldTypeTransfer.parseType(rs, i+1, resultSetMetaData.getColumnType(i+1), cfield);
				cfield.set(obj, data);
			}
			return obj;
		}
		
		pstmt.close();
		return null;
	}
	
	/**
	 * get the list from sql and class
	 * @param sql
	 * @param cla
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public <T> List<T> queryForList(String sql, Class<T> cla) throws Exception{
		List<T> list = new ArrayList<T>();
		
		initPstmt(sql);
		PreparedStatement pstmt = conn.prepareStatement(sql);
		ResultSetMetaData resultSetMetaData =  pstmt.getMetaData();
		rs = pstmt.executeQuery();
		
		Map<String, Field> fieldMap = null;
		int columnCount = resultSetMetaData.getColumnCount();
		
		while(rs.next()){
			if(cla == Integer.class || cla == String.class){
				list.add((T)rs.getObject(1));
				continue;
			}
			if(fieldMap == null){
				fieldMap = new HashMap<String, Field>();
				for(Field field : cla.getDeclaredFields()){
					field.setAccessible(true);
					fieldMap.put(field.getName().toUpperCase(), field);
				}
			}
			
			T obj = cla.newInstance();
			for(int i = 0; i < columnCount; i++){
				String columnName = resultSetMetaData.getColumnName(i+1).toUpperCase();
				Field cfield = fieldMap.get(columnName);
				if(cfield == null){
					continue;
				}
				Object data = DBFieldTypeTransfer.parseType(rs, i + 1, resultSetMetaData.getColumnType(i + 1), cfield);
				cfield.set(obj, data);
			}
			list.add(obj);
		}
		pstmt.close();
		
		return list;
	}
	
	//auto check the driver if driver is null
	protected void autoCheckDriver(){
		if(driverName != null){
			return;
		}
		driverName = databaseType!=null?databaseType.getDriver():null;
		if(databaseType == null){
			throw new IllegalArgumentException("数据库URL不正确,或者不支持该URL="+url);
		}
	}
	
	//may add some properties before open database
	protected void handleDatabaseInPreUse(){
		if(DatabaseEnum.ORACLE.equals(databaseType)){
			properties.put("remarksReporting", "true");
			schemaPattern = properties.getProperty("user").toUpperCase();
		}
	}
	
	public DBManager setDriverName(String driverName) {
		this.driverName = driverName;
		return this;
	}
	public DBManager setUrl(String url) {
		this.url = url;
		databaseType = DatabaseEnum.getDatabaseByUrl(url);
		return this;
	}
	
	public DBManager setUsername(String username) {
		properties.put("user", username);
		return this;
	}
	public DBManager setPassword(String password) {
		properties.put("password", password);
		return this;
	}
	
	public interface DBResultHandler{
		public void hanle(ResultSet rs) throws Exception;
	}
	
}
